﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SEO.Entity;
using System.Data;

namespace SEO.Business
{
    /// <summary>
    /// 关键字收录情况
    /// </summary>
    public class BisWebSEO
    {
        /// <summary>
        /// 根据网址获取ID
        /// </summary>
        /// <param name="strWebUrl"></param>
        /// <returns></returns>
        public int GetWebSEOID(string strWebUrl)
        {
            object retVal = SqlHelper.ExecuteScalar(
                                SqlHelper.database,
                                CommandType.Text,
                                "select top 1 [ID] from [WebSEO] where [WebUrl]=@WebUrl",
                                new SqlParameter("@WebUrl", strWebUrl));
            return retVal == null ? 0 : Convert.ToInt32(retVal);
        }

        /// <summary>
        /// 根据网址获取
        /// </summary>
        /// <param name="strWebUrl"></param>
        /// <returns></returns>
        public DataTable GetWebSEO(int nSortID,int nKeywordsID, int nKeywordsID1, string strWebUrl,DateTime dBeginTime)
        {
            string strSql = @"select top 1 w.[ID],w.[WebTitle] from [WebSEO] w 
                            left join [KeysRelation] kr on w.[ID]=kr.[WebSEOID] 
                            left join [Keywords] k on kr.[KeywordsID]=k.[ID] 
                            left join [Medical] m on kr.[MedicalID]=m.[ID] 
                            where m.[Status]=1 and k.[SortID]=@SortID and w.[WebUrl]=@WebUrl and kr.[CreateTime]>@CreateTime{0}";

            string strWhere=string.Empty;
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@SortID", nSortID));
            parms.Add(new SqlParameter("@WebUrl", strWebUrl));
            parms.Add(new SqlParameter("@CreateTime", dBeginTime));
            if (nKeywordsID > 0)
            {
                parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
                strWhere += "k.[ID]=@KeywordsID";
            }
            if (nKeywordsID1 > 0)
            {
                parms.Add(new SqlParameter("@KeywordsID1", nKeywordsID1));
                strWhere +=(strWhere==string.Empty?"":" or ")+"k.[ID]=@KeywordsID1";
            }
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " and (" + strWhere + ")";
            strSql=string.Format(strSql,strWhere);
            return SqlHelper.ExecuteDataset(
                    SqlHelper.database,
                    CommandType.Text,
                    strSql,
                    parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 根据网址获取ID
        /// </summary>
        /// <param name="strWebUrl"></param>
        /// <returns></returns>
        public bool CheckWebSEO(string strWebUrl, DateTime dDateTime)
        {
            object retVal = SqlHelper.ExecuteScalar(
                                SqlHelper.database,
                                CommandType.Text,
                                "select top 1 [ID] from [WebSEO] where [WebUrl]=@WebUrl and [CreateTime]<@CreateTime",
                                new SqlParameter("@WebUrl", strWebUrl),
                                new SqlParameter("@CreateTime", dDateTime));
            return retVal == null ? false : true;
        }

        ///// <summary>
        ///// 根据网址获取ID
        ///// </summary>
        ///// <param name="strWebUrl"></param>
        ///// <param name="dBegin"></param>
        ///// <param name="dEnd"></param>
        ///// <returns></returns>
        //public int GetWebSEOID(string strWebUrl, DateTime dBeginTime, DateTime dEndTime)
        //{
        //    string strSql = "select top 1 [ID] from [WebSEO] where [WebUrl]=@WebUrl and [CreateTime] between @BeginTime and @EndTime";
        //    List<SqlParameter> parms = new List<SqlParameter>();
        //    parms.Add(new SqlParameter("@WebUrl", strWebUrl));
        //    parms.Add(new SqlParameter("@BeginTime", dBeginTime));
        //    parms.Add(new SqlParameter("@EndTime", dEndTime));
        //    object retVal = SqlHelper.ExecuteScalar(SqlHelper.database,CommandType.Text,strSql,parms.ToArray());
        //    return retVal == null ? 0 : Convert.ToInt32(retVal);
        //}

        /// <summary>
        /// 获取关键字收录情况列表
        /// </summary>
        /// <param name="nMedicalID"></param>
        /// <param name="strKeywordsIDList"></param>
        /// <param name="dBeginTime"></param>
        /// <param name="dEndTime"></param>
        /// <returns></returns>
        public DataTable GetWebSEOListByDayIndex(int nMedicalID, int nKeywordsID, int nDayIndex, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = @"select w.[ID],krd.[PageIndex],krd.[RowIndex],krd.[AllIndex],[KeywordsID],[WebTitle],[WebUrl],krd.[CreateTime] from [WebSEO] w 
                            left join [KeysRelation] kr on w.[ID]=kr.[WebSEOID] 
                            left join [KeysRelationDay] krd on kr.[ID]=krd.[KeysRelationID]
                            where [MedicalID]=@MedicalID and [KeywordsID]=@KeywordsID and krd.[DayIndex]=@DayIndex and krd.[CreateTime] between @BeginTime and @EndTime order by krd.[AllIndex]";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@MedicalID", nMedicalID));
            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
        }

//        /// <summary>
//        /// 获取关键字收录情况列表
//        /// </summary>
//        /// <param name="nMedicalID"></param>
//        /// <param name="strKeywordsIDList"></param>
//        /// <param name="dBeginTime"></param>
//        /// <param name="dEndTime"></param>
//        /// <returns></returns>
//        public DataTable GetWebSEOList(int nMedicalID, int nKeywordsID, DateTime dBeginTime, DateTime dEndTime)
//        {
//            string strSql = @"select w.[ID],[PageIndex],[RowIndex],[AllIndex],[FinalPageIndex],[KeywordsID],[WebTitle],[WebUrl],kr.[CreateTime] from [WebSEO] w 
//                            left join [KeysRelation] kr on w.[ID]=kr.[WebSEOID] 
//                            where [MedicalID]=@MedicalID and [KeywordsID]=@KeywordsID and [FinalPageIndex]<3 and kr.[CreateTime] between @BeginTime and @EndTime order by AllIndex";
//            List<SqlParameter> parms = new List<SqlParameter>();
//            parms.Add(new SqlParameter("@MedicalID", nMedicalID));
//            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
//            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
//            parms.Add(new SqlParameter("@EndTime", dEndTime));
//            return SqlHelper.ExecuteDataset(
//                             SqlHelper.database,
//                             CommandType.Text,
//                             strSql,
//                             parms.ToArray()).Tables[0];
//        }


        /// <summary>
        /// 获取关键字收录情况列表
        /// </summary>
        /// <param name="nMedicalID"></param>
        /// <param name="strKeywordsIDList"></param>
        /// <param name="dBeginTime"></param>
        /// <param name="dEndTime"></param>
        /// <returns></returns>
        public DataTable GetWebSEOList(int nMedicalID, string strKeywordsIDList, int nDayIndex, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = @"select w.[ID],krd.[PageIndex],krd.[RowIndex],krd.[AllIndex],[KeywordsID],[WebTitle],[WebUrl],krd.[CreateTime] from [WebSEO] w 
                            left join [KeysRelation] kr on w.[ID]=kr.[WebSEOID] 
                            left join [KeysRelationDay] krd on kr.[ID]=krd.[KeysRelationID]
                            where [MedicalID]=@MedicalID and [KeywordsID] in(" + strKeywordsIDList + ") and krd.[DayIndex]=@DayIndex and krd.[CreateTime] between @BeginTime and @EndTime order by krd.[AllIndex]";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@MedicalID", nMedicalID));
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 获取关系
        /// </summary>
        /// <param name="nMedicalID"></param>
        /// <param name="nKeywordsID"></param>
        /// <param name="nWebSEOID"></param>
        /// <returns></returns>
        public DataTable GetKeysRelation(int nMedicalID, int nKeywordsID, int nWebSEOID, DateTime dBeginTime, DateTime dEndTime)
        {
            string strSql = "select top 1 [ID],[FinalPageIndex] from [KeysRelation] where [MedicalID]=@MedicalID and [KeywordsID]=@KeywordsID and [WebSEOID]=@WebSEOID and [CreateTime] between @BeginTime and @EndTime";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@MedicalID", nMedicalID));
            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
            parms.Add(new SqlParameter("@WebSEOID", nWebSEOID));
            parms.Add(new SqlParameter("@BeginTime", dBeginTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 新增关系
        /// </summary>
        /// <param name="nMedicalID"></param>
        /// <param name="nKeywordsID"></param>
        /// <param name="nWebSEOID"></param>
        /// <returns></returns>
        public int InsertKeysRelation(int nMedicalID, int nKeywordsID, int nWebSEOID, int nPageIndex, int nRowIndex, int nAllIndex, int nDayIndex, int nFinalPageIndex,DateTime dCreateTime)
        {
            string strSql = "insert into [KeysRelation]([MedicalID],[KeywordsID],[WebSEOID],[PageIndex],[RowIndex],[AllIndex],[DayIndex],[FinalPageIndex],[CreateTime]) values(@MedicalID,@KeywordsID,@WebSEOID,@PageIndex,@RowIndex,@AllIndex,@DayIndex,@FinalPageIndex,@CreateTime)";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@MedicalID", nMedicalID));
            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
            parms.Add(new SqlParameter("@WebSEOID", nWebSEOID));
            parms.Add(new SqlParameter("@PageIndex", nPageIndex));
            parms.Add(new SqlParameter("@RowIndex", nRowIndex));
            parms.Add(new SqlParameter("@AllIndex", nAllIndex));
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@FinalPageIndex", nFinalPageIndex));
            parms.Add(new SqlParameter("@CreateTime", dCreateTime));
            return SqlHelper.ExecuteQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray());
        }

        /// <summary>
        /// 更新关系
        /// </summary>
        /// <param name="nID"></param>
        /// <param name="nFinalPageIndex"></param>
        /// <returns></returns>
        public bool UpdateKeysRelation(int nID, int nFinalPageIndex)
        {
            string strSql = "update [KeysRelation] set [FinalPageIndex]=@FinalPageIndex where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@FinalPageIndex", nFinalPageIndex));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 新增关系
        /// </summary>
        /// <param name="nKeysRelationID"></param>
        /// <param name="nPageIndex"></param>
        /// <param name="nRowIndex"></param>
        /// <param name="nAllIndex"></param>
        /// <param name="nDayIndex"></param>
        /// <returns></returns>
        public bool InsertKeysRelationDay(int nKeysRelationID, int nPageIndex, int nRowIndex, int nAllIndex, int nDayIndex, DateTime dCreateTime)
        {
            string strSql = "insert into [KeysRelationDay]([KeysRelationID],[PageIndex],[RowIndex],[AllIndex],[DayIndex],[CreateTime]) values(@KeysRelationID,@PageIndex,@RowIndex,@AllIndex,@DayIndex,@CreateTime)";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@KeysRelationID", nKeysRelationID));
            parms.Add(new SqlParameter("@PageIndex", nPageIndex));
            parms.Add(new SqlParameter("@RowIndex", nRowIndex));
            parms.Add(new SqlParameter("@AllIndex", nAllIndex));
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@CreateTime", dCreateTime));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 新增关系
        /// </summary>
        /// <param name="nKeysRelationID"></param>
        /// <param name="nPageIndex"></param>
        /// <param name="nRowIndex"></param>
        /// <param name="nAllIndex"></param>
        /// <param name="nDayIndex"></param>
        /// <returns></returns>
        public bool DeleteKeysRelationDay(int nKeysRelationID, int nDayIndex, DateTime dCreateTime, DateTime dEndTime)
        {
            string strSql = "delete from [KeysRelationDay] where [KeysRelationID]=@KeysRelationID and [DayIndex]=@DayIndex and [CreateTime] between @CreateTime and @EndTime";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@KeysRelationID", nKeysRelationID));
            parms.Add(new SqlParameter("@DayIndex", nDayIndex));
            parms.Add(new SqlParameter("@CreateTime", dCreateTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        /// 新增关键字收录情况
        /// </summary>
        /// <param name="Info"></param>
        /// <returns></returns>
        public int Insert(WebSEO Info)
        {
            string strSql = "insert into [WebSEO]([BaiduUrl],[WebUrl],[WebTitle],[WebType],[WebInfo]) values(@BaiduUrl,@WebUrl,@WebTitle,@WebType,@WebInfo)";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@BaiduUrl", Info.BaiduUrl));
            parms.Add(new SqlParameter("@WebUrl", Info.WebUrl));
            parms.Add(new SqlParameter("@WebTitle", Info.WebTitle));
            parms.Add(new SqlParameter("@WebType", Info.WebType));
            parms.Add(new SqlParameter("@WebInfo", Info.WebInfo));
            return SqlHelper.ExecuteQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray());
        }

        /// <summary>
        /// 更新关键字
        /// </summary>
        /// <param name="Info"></param>
        /// <returns></returns>
        public bool Update(WebSEO Info)
        {
            string strSql = "update [WebSEO] set [WebTitle]=@WebTitle,[WebInfo]=@WebInfo where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@WebTitle", Info.WebTitle));
            parms.Add(new SqlParameter("@WebInfo", Info.WebInfo));
            parms.Add(new SqlParameter("@ID", Info.ID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }

        /// <summary>
        ///  按时间分类获取指定记录的收录情况
        /// </summary>
        /// <param name="nWebSEOID"></param>
        /// <param name="dBeginTime"></param>
        /// <returns></returns>
        public DataTable GetWebSEOList(int nWebSEOID, int nKeywordsID, int nKeywordsID1, DateTime dCreateTime, DateTime dEndTime)
        {
            string strSql = @"select kr.[ID],[WebSEOID],[FinalPageIndex],kr.[CreateTime] from [KeysRelation] kr 
                            where [WebSEOID]=@WebSEOID and (@KeywordsID or [KeywordsID]=@KeywordsID or @KeywordsID1=0 or [KeywordsID]=@KeywordsID1) and kr.[CreateTime] between @CreateTime and @EndTime order by [FinalPageIndex] desc";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@WebSEOID", nWebSEOID));
            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
            parms.Add(new SqlParameter("@KeywordsID1", nKeywordsID1));
            parms.Add(new SqlParameter("@CreateTime", dCreateTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
        }

        /// <summary>
        ///  按时间分类获取指定记录的收录情况
        /// </summary>
        /// <param name="nWebSEOID"></param>
        /// <param name="dBeginTime"></param>
        /// <returns></returns>
        public DataTable GetWebSEOReportList(int nWebSEOID, int nKeywordsID, DateTime dCreateTime, DateTime dEndTime)
        {
            string strSql = @"select kr.[ID],[WebSEOID],[FinalPageIndex],kr.[CreateTime] from [KeysRelation] kr 
                            left join [Medical] m on kr.[MedicalID]=m.[ID]
                            where [WebSEOID]=@WebSEOID and m.[Status]=1 and [KeywordsID]=@KeywordsID and [FinalPageIndex]<3 and kr.[CreateTime] between @CreateTime and @EndTime order by [FinalPageIndex] desc";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@WebSEOID", nWebSEOID));
            parms.Add(new SqlParameter("@KeywordsID", nKeywordsID));
            parms.Add(new SqlParameter("@CreateTime", dCreateTime));
            parms.Add(new SqlParameter("@EndTime", dEndTime));
            return SqlHelper.ExecuteDataset(
                             SqlHelper.database,
                             CommandType.Text,
                             strSql,
                             parms.ToArray()).Tables[0];
        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllList()
        {
            return SqlHelper.ExecuteDataset(SqlHelper.database, CommandType.Text, "select top 30 * from [WebSEO] order by [ID]").Tables[0];
        }

        /// <summary>
        /// 更新Url
        /// </summary>
        /// <param name="nID"></param>
        /// <param name="nFinalPageIndex"></param>
        /// <returns></returns>
        public bool UpdateWebUrl(int nID, string strWebUrl)
        {
            string strSql = "update [WebSEO] set [WebUrl]=@WebUrl where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@WebUrl", strWebUrl));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }


        /// <summary>
        /// 更新类型
        /// </summary>
        /// <param name="nID"></param>
        /// <param name="nFinalPageIndex"></param>
        /// <returns></returns>
        public bool UpdateWebType(int nID, int nWebType)
        {
            string strSql = "update [WebSEO] set [WebType]=@WebType where [ID]=@ID";
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@WebType", nWebType));
            parms.Add(new SqlParameter("@ID", nID));
            return SqlHelper.ExecuteNonQuery(SqlHelper.database, CommandType.Text, strSql, parms.ToArray()) > 0;
        }
    }
}
